import sqlite3
import os
from typing import Generator
import threading
from contextlib import contextmanager

# 获取当前文件所在目录
BASE_DIR = os.path.dirname(os.path.abspath(__file__))
DATABASE_URL = os.path.join(BASE_DIR, "scoring_system.db")

def get_db() -> Generator[sqlite3.Connection, None, None]:
    """获取数据库连接 - 线程安全版本"""
    # 为每个请求创建新的数据库连接，并禁用线程检查
    conn = sqlite3.connect(DATABASE_URL, check_same_thread=False)
    conn.row_factory = sqlite3.Row  # 让查询结果可以通过列名访问
    # 启用外键约束
    conn.execute("PRAGMA foreign_keys = ON")
    try:
        yield conn
    finally:
        conn.close()

@contextmanager
def get_db_context():
    """获取数据库连接的上下文管理器"""
    conn = sqlite3.connect(DATABASE_URL, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    try:
        yield conn
    finally:
        conn.close()

def get_db_sync() -> sqlite3.Connection:
    """获取同步数据库连接 - 用于初始化等场景"""
    conn = sqlite3.connect(DATABASE_URL)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn

def init_db():
    """初始化数据库表"""
    try:
        conn = get_db_sync()
        cursor = conn.cursor()
        
        # 创建评委表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS judges (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT UNIQUE NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 创建选手表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS contestants (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                number TEXT UNIQUE NOT NULL,
                name TEXT NOT NULL,
                is_active BOOLEAN DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 创建评分表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS scores (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                judge_id INTEGER NOT NULL,
                contestant_id INTEGER NOT NULL,
                score REAL NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (judge_id) REFERENCES judges (id) ON DELETE CASCADE,
                FOREIGN KEY (contestant_id) REFERENCES contestants (id) ON DELETE CASCADE,
                UNIQUE(judge_id, contestant_id)
            )
        ''')
        
        conn.commit()
        conn.close()
        print("数据库初始化成功")
    except Exception as e:
        print(f"数据库初始化失败: {e}")
        # 如果数据库文件损坏，尝试删除并重新创建
        import os
        if os.path.exists(DATABASE_URL):
            try:
                os.remove(DATABASE_URL)
                print("已删除损坏的数据库文件，将重新创建")
                init_db()  # 递归调用重新创建
            except Exception as e2:
                print(f"删除数据库文件失败: {e2}")

# 初始化数据库
init_db() 